package com.susem.mw.service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import javax.jws.WebMethod;
import javax.jws.WebParam;
import javax.jws.WebService;

import com.susem.mw.types.ProposalComment;
import com.susem.mw.types.Thesis;
import com.susem.mw.types.ThesisProposal;
import com.susem.mw.types.ThesisReview;
import com.susem.mw.types.ThesisProposal.Status;

@WebService(name = "ThesisWebService")
public class ThesisWsBean {
	Connection conn = null;

	@WebMethod(operationName = "saveThesisProposal")
	public int saveThesisProposal(@WebParam(name = "proposal") ThesisProposal proposal) {
		int newThesisProposalID = 0;
		try {
			int professor_id = 0;
			int student_id = 0;
			connectToDB();
			Statement statement = conn.createStatement();
			
			//validate input
			if(proposal.getAnnotation() == null ||
					proposal.getAnnotation().trim().length() == 0) {
				proposal.setAnnotation("N/A");
			}
			if(proposal.getConsultant() == null ||
					proposal.getConsultant().trim().length() == 0) {
				proposal.setConsultant("N/A");
			}
			if(proposal.getDesription() == null ||
					proposal.getDesription().trim().length() ==0) {
				proposal.setDesription("N/A");
			}
			if(proposal.getExecutionTime()<1 || proposal.getExecutionTime()>70) {
				proposal.setExecutionTime(70);
			}
			if(proposal.getProgramming() == null ||
					proposal.getProgramming().trim().length() == 0) {
				proposal.setProgramming("N/A");
			}
			if(proposal.getStudent() == null ||
					proposal.getStudent().trim().length() == 0) {
				proposal.setStudent("N/A");
			}
			if(proposal.getSupervisor() == null ||
					proposal.getSupervisor().trim().length() == 0) {
				proposal.setSupervisor("Unassigned");
			}
			if(proposal.getTitle() == null ||
					proposal.getTitle().trim().length() == 0) {
				proposal.setTitle("N/A");
			}
			
			//read or create professor
			ResultSet result = statement.executeQuery("SELECT id"
					+ " FROM professors WHERE name='"
					+ proposal.getSupervisor() + "'");
			if (result.next()) {
				professor_id = result.getInt("id");
			} else {
				statement.executeUpdate("INSERT INTO  professors ("
						+ " id , name ) VALUES ( NULL , '"
						+ proposal.getSupervisor() + "' );", Statement.RETURN_GENERATED_KEYS);
				ResultSet rs = statement.getGeneratedKeys();
				if (rs.next()) {
					professor_id = rs.getInt(1);
				}
			}
			
			//create student
			statement.executeUpdate("INSERT INTO  students ("
					+ " id , name ) VALUES ( NULL , '"
					+ proposal.getStudent() + "' );", Statement.RETURN_GENERATED_KEYS);
			ResultSet rs = statement.getGeneratedKeys();
			if (rs.next()) {
				student_id = rs.getInt(1);
			}
			
			Timestamp submitDate = new Timestamp(System.currentTimeMillis());
			
			//create the thesis proposal itself
			statement.executeUpdate("INSERT INTO  proposals ("
					+ " id , student, supervisor, status_label, title, annotation," + 
					" description, programming, execution_time, submitted, consultant_name )" + 
					" VALUES ( NULL , '" 
					+ student_id + "', ' " 
					+ professor_id + "', 'waiting', ' "
					+ proposal.getTitle() + "', ' "
					+ proposal.getAnnotation() + "', ' "
					+ proposal.getDesription() + "', ' "
					+ proposal.getProgramming() + "', ' "
					+ proposal.getExecutionTime() + "', ' "
					+ submitDate + "', ' "
					+ proposal.getConsultant() + "' );", 
					Statement.RETURN_GENERATED_KEYS);
			ResultSet insertResult = statement.getGeneratedKeys();
			if (insertResult.next()) {
				newThesisProposalID = insertResult.getInt(1);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			newThesisProposalID = 0;
		} finally {
			closeDBConnection();
		}
		return newThesisProposalID;
	}

	@WebMethod(operationName = "getWaitingProposals")
	public ThesisProposal[] getWaitingProposals() {
		List<ThesisProposal> result = new ArrayList<ThesisProposal>();
		try {
			connectToDB();
			Statement statement = conn.createStatement();
			ResultSet queryResult = statement.executeQuery("SELECT proposals.status_label, " 
				+ " proposals.title, proposals.annotation, proposals.description, proposals.programming, " 
				+ " proposals.execution_time, proposals.submitted, proposals.consultant_name, " 
				+ " students.name AS student_name, professors.name AS professor_name"
				+ " FROM proposals, students, professors WHERE status_label='waiting'" 
				+ " AND students.id=proposals.student AND professors.id=proposals.supervisor");
			while(queryResult.next()) {
				//construct ThesisProposal object
				ThesisProposal newProposal = new ThesisProposal();
				newProposal.setAnnotation(queryResult.getString("annotation"));
				newProposal.setConsultant(queryResult.getString("consultant_name"));
				newProposal.setDesription(queryResult.getString("description"));
				newProposal.setExecutionTime(queryResult.getInt("execution_time"));
				newProposal.setProgramming(queryResult.getString("programming"));
				newProposal.setStatus(Status.waiting);
				newProposal.setSubmitted(queryResult.getTimestamp("submitted"));
				newProposal.setTitle(queryResult.getString("title"));
				newProposal.setStudent(queryResult.getString("student_name"));
				newProposal.setSupervisor(queryResult.getString("professor_name"));
				result.add(newProposal);
			}
			return result.toArray(new ThesisProposal[result.size()]);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeDBConnection();
		}
		
		return new ThesisProposal[0];
	}
	
	@WebMethod(operationName = "changeProposalStatus")
	public int changeProposalStatus(@WebParam(name = "proposalID") int proposalID,
			@WebParam(name = "proposalStatus") Status proposalStatus) {
		int newThesisID = 0;
		try {
			connectToDB();
			
			//validate input
			if(proposalStatus == null) {
				proposalStatus = Status.waiting;
			}
			//update Proposal status
			PreparedStatement statement = conn.prepareStatement( "UPDATE  proposals 	"
					+ "SET status_label=?" + 
					" WHERE id='" + proposalID + "'", 
					Statement.RETURN_GENERATED_KEYS);
			statement.setString(1, proposalStatus.toString());
			statement.executeUpdate();
			
			//if Proposal is accepted, create Thesis record based on this proposal
			if(proposalStatus == Status.accepted) {
				Statement select = conn.createStatement();
				ResultSet queryResult = select.executeQuery("SELECT *"
						+ " FROM proposals WHERE id='" + proposalID + "'");
				if(queryResult.next()) {
					 newThesisID = this.createThesis(
							queryResult.getInt("student"), 
							queryResult.getInt("supervisor"), 
							queryResult.getString("title"), 
							queryResult.getString("annotation"));
				}
			}
			
			return newThesisID;
		} catch (Exception e) {
			e.printStackTrace();
			newThesisID = 0;
		} finally {
			closeDBConnection();
		}
		
		return newThesisID;
	}
	
	@WebMethod(operationName = "submitThesis")
	public int submitThesis(@WebParam(name = "thesisID") int thesisID,
			@WebParam(name = "thesis") Thesis thesis) {
		int submittedThesisID = 0;
		try {
			connectToDB();
			
			//validate user input
			if(thesis.getDepartment() == null ||
					thesis.getDepartment().trim().length() == 0) {
				thesis.setDepartment("N/A");
			}
			if(thesis.getFaculty() == null ||
					thesis.getFaculty().trim().length() == 0) {
				thesis.setFaculty("N/A");
			}
			if(thesis.getDocument() == null) {
				thesis.setDocument(new byte[0]);
			}
			if(thesis.getDocumentName() == null ||
					thesis.getDocumentName().trim().length() == 0) {
				thesis.setDocumentName("untitled.txt");
			}
			if(thesis.getGraduationDate() == null) {
				thesis.setGraduationDate(new Timestamp(System.currentTimeMillis()));
			}
			
			//Save updated Thesis
			PreparedStatement statement = conn.prepareStatement( "UPDATE  thesis 	"
					+ "SET department=?, faculty=?, thesis_file=?, graduation_date=?, " + 
					" thesis_file_name=? WHERE id='" + thesisID + "'", 
					Statement.RETURN_GENERATED_KEYS);
			statement.setString(1, thesis.getDepartment());
			statement.setString(2, thesis.getFaculty());
			statement.setBytes(3, thesis.getDocument());
			statement.setTimestamp(4, new Timestamp(thesis.getGraduationDate().getTime()));
			statement.setString(5, thesis.getDocumentName());
			statement.executeUpdate();
			
			submittedThesisID = thesisID;
		} catch (Exception e) {
			e.printStackTrace();
			submittedThesisID = 0;
		} finally {
			closeDBConnection();
		}
		
		return submittedThesisID;
	}
	
	@WebMethod(operationName = "saveThesisReview")
	public int saveThesisReview(@WebParam(name = "thesisReview") ThesisReview thesisReview) {
		int newThesisReviewID = 0;
		try {
			int professorID = 0;
			connectToDB();
			Statement statement = conn.createStatement();
			
			//validate input
			if(thesisReview.getComments() == null ||
					thesisReview.getComments().trim().length() == 0) {
				thesisReview.setComments("N/A");
			}
			if(thesisReview.getConclusion() == null ||
					thesisReview.getConclusion().trim().length() == 0) {
				thesisReview.setConclusion("N/A");
			}
			if(thesisReview.getContributions() == null ||
					thesisReview.getContributions().trim().length() == 0) {
				thesisReview.setContributions("N/A");
			}
			if(thesisReview.getDescription() == null ||
					thesisReview.getDescription().trim().length() == 0) {
				thesisReview.setDescription("N/A");
			}
			if(thesisReview.getLayout() == null ||
					thesisReview.getLayout().trim().length() == 0) {
				thesisReview.setLayout("N/A");
			}
			if(thesisReview.getMark() < 2 || thesisReview.getMark() > 6) {
				thesisReview.setMark(0);
			}
			if(thesisReview.getRecommendations() == null ||
					thesisReview.getRecommendations().trim().length() == 0) {
				thesisReview.setRecommendations("N/A");
			}
			if(thesisReview.getRelevance() == null ||
					thesisReview.getRelevance().trim().length() == 0) {
				thesisReview.setRelevance("N/A");
			}
			if(thesisReview.getReviewer() == null ||
					thesisReview.getReviewer().trim().length() == 0) {
				thesisReview.setReviewer("Unknown");
			}
			
			//get Reviewer ID
			ResultSet result = statement.executeQuery("SELECT id"
					+ " FROM professors WHERE name='"
					+ thesisReview.getReviewer() + "'");
			if (result.next()) {
				professorID = result.getInt("id");
			} else {
				statement.executeUpdate("INSERT INTO  professors ("
						+ " id , name ) VALUES ( NULL , '"
						+ thesisReview.getReviewer() + "' );", Statement.RETURN_GENERATED_KEYS);
				ResultSet rs = statement.getGeneratedKeys();
				if (rs.next()) {
					professorID = rs.getInt(1);
				}
			}
			
			Timestamp reviewDate = new Timestamp(System.currentTimeMillis());
			
			//create the thesis review itself
			statement.executeUpdate("INSERT INTO  reviews ("
					+ " id , thesis_id, reviewer, review_date, relevance, description, layout," + 
					" contributions, recommendations, comments, conclusion, mark )" + 
					" VALUES ( NULL , '" 
					+ thesisReview.getThesisID() + "', ' " 
					+ professorID + "', ' "
					+ reviewDate + "', ' "
					+ thesisReview.getRelevance() + "', ' "
					+ thesisReview.getDescription() + "', ' "
					+ thesisReview.getLayout() + "', ' "
					+ thesisReview.getContributions() + "', ' "
					+ thesisReview.getRecommendations() + "', ' "
					+ thesisReview.getComments() + "', ' "
					+ thesisReview.getConclusion() + "', ' "
					+ thesisReview.getMark() + "' );", 
					Statement.RETURN_GENERATED_KEYS);
			ResultSet insertResult = statement.getGeneratedKeys();
			if (insertResult.next()) {
				newThesisReviewID = insertResult.getInt(1);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			newThesisReviewID = 0;
		} finally {
			closeDBConnection();
		}
		return newThesisReviewID;
	}
	
	@WebMethod(operationName = "saveProposalComment")
	public int saveProposalComment(@WebParam(name = "proposalComment") ProposalComment proposalComment) {
		int newProposalCommentID = 0;
		try {
			int professorID = 0;
			connectToDB();
			Statement statement = conn.createStatement();
			
			//validate input
			if(proposalComment.getDescription() == null ||
					proposalComment.getDescription().trim().length() == 0) {
				proposalComment.setDescription("N/A");
			}
			if(proposalComment.getProfessor() == null ||
					proposalComment.getProfessor().trim().length() == 0) {
				proposalComment.setProfessor("Unknown");
			}
			
			//get Reviewer ID
			ResultSet result = statement.executeQuery("SELECT id"
					+ " FROM professors WHERE name='"
					+ proposalComment.getProfessor() + "'");
			if (result.next()) {
				professorID = result.getInt("id");
			} else {
				statement.executeUpdate("INSERT INTO  professors ("
						+ " id , name ) VALUES ( NULL , '"
						+ proposalComment.getProfessor() + "' );", Statement.RETURN_GENERATED_KEYS);
				ResultSet rs = statement.getGeneratedKeys();
				if (rs.next()) {
					professorID = rs.getInt(1);
				}
			}
			
			Timestamp commentDate = new Timestamp(System.currentTimeMillis());
			
			//create the thesis review itself
			statement.executeUpdate("INSERT INTO  comments ("
					+ " id , proposal_id, professor, description, comment_date )" + 
					" VALUES ( NULL , '" 
					+ proposalComment.getProposalID() + "', ' " 
					+ professorID + "', ' "
					+ proposalComment.getDescription() + "', ' "
					+ commentDate + "' );", 
					Statement.RETURN_GENERATED_KEYS);
			ResultSet insertResult = statement.getGeneratedKeys();
			if (insertResult.next()) {
				newProposalCommentID = insertResult.getInt(1);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			newProposalCommentID = 0;
		} finally {
			closeDBConnection();
		}
		return newProposalCommentID;
	}
	
	private int createThesis(int studentID, int profID, String title,
			String annotation) {
		int newThesisID = 0;
		try {
			connectToDB();
			Statement statement = conn.createStatement();
			statement.executeUpdate("INSERT INTO  thesis ("
					+ " id , student, supervisor, title, annotation," + 
					" department, faculty, thesis_file, thesis_file_name, graduation_date )" + 
					" VALUES ( NULL , '" 
					+ studentID + "', ' " 
					+ profID + "', ' "
					+ title + "', ' "
					+ annotation + "', ' "
					+ "N/A', 'N/A', ' "
					+ new byte[0] + "', ' "
					+ " untitled.txt ', ' "
					+ new Timestamp(System.currentTimeMillis()) + "' );", 
					Statement.RETURN_GENERATED_KEYS);
			ResultSet rs = statement.getGeneratedKeys();
			if (rs.next()) {
				newThesisID = rs.getInt(1);
			}
			
			return newThesisID;
		} catch (Exception e) {
			e.printStackTrace();
			newThesisID = 0;
		} finally {
			closeDBConnection();
		}
		
		return newThesisID;
	}

	private void connectToDB() throws Exception {
		String userName = "root";
		String password = "root";
		String url = "jdbc:mysql://localhost:3306/middleware";
		Class.forName("com.mysql.jdbc.Driver").newInstance();
		conn = DriverManager.getConnection(url, userName, password);
		System.out.println("Database connection established");
	}

	private void closeDBConnection() {
		if (conn != null) {
			try {
				conn.close();
				System.out.println("Database connection terminated");
			} catch (Exception e) { /* ignore close errors */
			}
		}
	}
}
